﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.Data;
using System.Windows.Forms;
using NPOI.XSSF.UserModel;

namespace XF.Common
{
    public class ExcelOptioner
    {
        /// <summary>  
        /// DataTable导出到Excel的MemoryStream Export()  
        /// </summary>  
        /// <param name="dtSource">DataTable数据源</param>  
        /// <param name="strHeaderText">Excel表头文本（例如：车辆列表）</param>  
        public static bool Export(DataTable dt, string filePath, ref string msg)
        {
            try
            {
                bool isCompatible = GetIsCompatible(filePath);
                IWorkbook workbook = CreateWorkbook(isCompatible);
                ISheet sheet = workbook.CreateSheet();

                int rowIndex = 0;
                foreach (DataRow row in dt.Rows)
                {
                    #region 新建表，填充表头，填充列头，样式
                    if (rowIndex == 65535 || rowIndex == 0)
                    {
                        //若数据超量则新增工作簿并填充表头
                        if (rowIndex != 0)
                        {
                            sheet = workbook.CreateSheet();
                        }
                        #region 列头及样式
                        IRow headerRow = sheet.CreateRow(0);
                        ICellStyle headStyle = GetHeadStyle(workbook);
                        foreach (DataColumn column in dt.Columns)
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                        }
                        #endregion
                        rowIndex = 1;
                    }
                    #endregion

                    #region 填充内容
                    IRow dataRow = sheet.CreateRow(rowIndex);

                    foreach (DataColumn column in dt.Columns)
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue((row[column.Ordinal] ?? "").ToString());
                    }
                    #endregion

                    rowIndex++;
                }


                //设置自动列宽
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    sheet.AutoSizeColumn(i);
                }

                //输出文件
                using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
                {
                    workbook.Write(fs);
                    fs.Dispose();
                    workbook = null;
                    msg = "导出成功";
                    return true;
                }
            }
            catch (Exception ex)
            {
                msg = "导出失败，失败原因：" + ex.Message;
                return false;
            }
        }


        /// <summary>  
        /// DataTable导出到Excel的MemoryStream Export()  
        /// </summary>  
        /// <param name="dgv">DataGridView数据源</param>  
        /// <param name="filePath">导出路径</param>  
        /// <param name="msg">返回的提示信息</param>  
        /// <param name="bExBackColor">是否导出背景颜色（常用颜色）</param>  
        public static bool Export(DataGridView dgv, string filePath, ref string msg, bool bExBackColor = false, Type[] dataTypes = null)
        {
            try
            {
                //创建工作簿
                bool isCompatible = GetIsCompatible(filePath);
                IWorkbook workbook = CreateWorkbook(isCompatible);
                ISheet sheet = workbook.CreateSheet();

                int rowIndex = 0;
                Dictionary<string, ICellStyle> dicColorStyle = new Dictionary<string, ICellStyle>();

                //填充Excel表格
                foreach (DataGridViewRow row in dgv.Rows)
                {
                    #region 新建表，填充表头，填充列头，样式
                    if (rowIndex == 65535 || rowIndex == 0)
                    {
                        //若数据超量则新增工作簿并填充表头
                        if (rowIndex != 0)
                        {
                            sheet = workbook.CreateSheet();
                        }
                        #region 列头及样式
                        IRow headerRow = sheet.CreateRow(0);
                        ICellStyle headStyle = GetHeadStyle(workbook);
                        foreach (DataGridViewColumn column in dgv.Columns)
                        {
                            headerRow.CreateCell(column.Index).SetCellValue(column.HeaderText);
                            headerRow.GetCell(column.Index).CellStyle = headStyle;
                        }
                        #endregion
                        rowIndex = 1;
                    }
                    #endregion

                    #region 填充内容
                    IRow dataRow = sheet.CreateRow(rowIndex);

                    foreach (DataGridViewColumn column in dgv.Columns)
                    {
                        ICell newCell = dataRow.CreateCell(column.Index);

                        if (bExBackColor)
                        {
                            string colorName = row.Cells[column.Name].Style.BackColor.Name;
                            try
                            {
                                ICellStyle outValue;
                                if (colorName != "0" && !dicColorStyle.TryGetValue(colorName, out outValue))
                                {
                                    outValue = workbook.CreateCellStyle();
                                    outValue.FillForegroundColor = ColorValue.GetColorValue(colorName);
                                    outValue.FillPattern = FillPattern.SolidForeground;
                                    dicColorStyle.Add(colorName, outValue);
                                }
                                if (colorName != "0")
                                    newCell.CellStyle = dicColorStyle[colorName];
                            }
                            catch (Exception err)
                            {

                            }
                        }
                        Type t = null;
                        if (dataTypes != null && column.Index < dataTypes.Length)
                        {
                            t = dataTypes[column.Index];
                        }
                        FillCellValue(newCell, t, row.Cells[column.Index].FormattedValue);
                    }
                    #endregion

                    rowIndex++;
                }

                //设置自动列宽
                for (int i = 0; i < dgv.Columns.Count; i++)
                {
                    sheet.AutoSizeColumn(i);
                }

                //输出文件
                using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
                {
                    workbook.Write(fs);
                    fs.Dispose();
                    workbook = null;
                    msg = "导出成功";
                    return true;
                }
            }
            catch (Exception ex)
            {
                msg = "导出失败，失败原因：" + ex.Message;
                return false;
            }
        }

        private static ICellStyle GetHeadStyle(IWorkbook workbook)
        {
            ICellStyle headStyle = workbook.CreateCellStyle();
            headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = 10;
            font.IsBold = true;
            headStyle.SetFont(font);
            return headStyle;
        }

        /// <summary>  
        /// 读取excel ,默认第一行为标头  
        /// </summary>  
        /// <param name="strFileName">excel文档路径</param>  
        /// <returns></returns>  
        public static DataTable Import(string strFileName, int rowIndex = 0)
        {
            DataTable dt = new DataTable();
            IWorkbook hssfworkbook = CreateWorkbook(strFileName);

            ISheet sheet = hssfworkbook.GetSheetAt(0);

            IRow headerRow = sheet.GetRow(rowIndex);
            int cellCount = headerRow.LastCellNum;

            //构建Table列
            for (int j = 0; j < cellCount; j++)
            {
                ICell cell = headerRow.GetCell(j);
                string sColName = cell.ToString();
                //防止列重复，自动在后面追加文本区分
                int i = 1;
                while (dt.Columns.Contains(sColName))
                {
                    sColName = cell.ToString() + "(" + i.ToString() + ")";
                    i++;
                }
                dt.Columns.Add(sColName);
            }

            //构建Table数据
            for (int i = (rowIndex + 1); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = dt.NewRow();
                if (row != null)
                {
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
                            dataRow[j] = row.GetCell(j).ToString();
                    }
                    dt.Rows.Add(dataRow);
                }
            }
            return dt;
        }

        /// <summary>
        /// 判断是否为兼容模式
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        private static bool GetIsCompatible(string filePath)
        {
            return filePath.EndsWith(".xls", StringComparison.OrdinalIgnoreCase);
        }


        private static IWorkbook CreateWorkbook(string strFileName)
        {
            IWorkbook hssfworkbook;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                bool isCompatible = GetIsCompatible(strFileName);
                hssfworkbook = CreateWorkbook(isCompatible, file);
            }
            return hssfworkbook;
        }

        /// <summary>
        /// 创建工作薄
        /// </summary>
        /// <param name="isCompatible"></param>
        /// <returns></returns>
        private static IWorkbook CreateWorkbook(bool isCompatible)
        {
            if (isCompatible)
            {
                return new HSSFWorkbook();
            }
            else
            {
                return new XSSFWorkbook();
            }
        }

        /// <summary>
        /// 创建工作薄(依据文件流)
        /// </summary>
        /// <param name="isCompatible"></param>
        /// <param name="stream"></param>
        /// <returns></returns>
        private static IWorkbook CreateWorkbook(bool isCompatible, dynamic stream)
        {
            if (isCompatible)
            {
                return new HSSFWorkbook(stream);
            }
            else
            {
                return new XSSFWorkbook(stream);
            }
        }

        private static void FillCellValue(ICell cell, Type type, object value)
        {
            switch (type?.ToString())
            {
                case "System.String"://字符串类型  
                    cell.SetCellValue(value.ToString());
                    break;
                case "System.DateTime"://日期类型  
                    System.DateTime dateV;
                    if (System.DateTime.TryParse(value.ToString(), out dateV))
                    {
                        cell.SetCellValue(dateV);
                    }
                    else
                    {
                        cell.SetCellValue(value.ToString());
                    }
                    break;
                case "System.Boolean"://布尔型  
                    bool boolV = false;
                    if (bool.TryParse(value.ToString(), out boolV))
                    {
                        cell.SetCellValue(boolV);
                    }
                    else
                    {
                        cell.SetCellValue(value.ToString());
                    }
                    break;
                case "System.Int16"://整型  
                case "System.Int32":
                case "System.Int64":
                case "System.Byte":
                    int intV = 0;
                    if (int.TryParse(value.ToString(), out intV))
                    {
                        cell.SetCellValue(intV);
                    }
                    else
                    {
                        cell.SetCellValue(value.ToString());
                    }
                    break;
                case "System.Decimal"://浮点型  
                case "System.Double":
                    double doubV = 0;
                    if (double.TryParse(value.ToString(), out doubV))
                    {
                        cell.SetCellValue(doubV);
                    }
                    else
                    {
                        cell.SetCellValue(value.ToString());
                    }
                    break;
                case "System.DBNull"://空值处理  
                    cell.SetCellValue("");
                    break;
                default:
                    cell.SetCellValue(value.ToString());
                    break;
            }
        }
    }
    public class ColorValue
    {
        public static short GetColorValue(string Name)
        {
            switch (Name)
            {
                case "Yellow":
                    return 13;
                case "Black":
                    return 8;
                case "Orange":
                    return 53;
                case "Green":
                    return 17;
                case "Blue":
                    return 12;
                case "Red":
                    return 10;
                case "Pink":
                    return 14;
                case "Gold":
                    return 51;
                case "White":
                    return 9;
                default:
                    return 0;
            }
        }

    }
}
